﻿CREATE PROCEDURE [util].[SetSQLAgentOperators](
	@OperatorXml xml
)
AS
BEGIN
	-- parse the operator xml 
	DECLARE @docHandle int;
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @OperatorXml;

	-- loop through each operator
	DECLARE C_OPERATORS CURSOR FOR(
		SELECT
			[Name]
			,ISNULL([Enabled], 1)
			,[EmailAddress]
			,[PagerAddress]
			,ISNULL([WeekdayPagerStartTime], 090000)
			,ISNULL([WeekdayPagerEndTime], 180000)
			,ISNULL([SaturdayPagerStartTime], 090000)
			,ISNULL([SaturdayPagerEndTime], 180000)
			,ISNULL([SundayPagerStartTime], 090000)
			,ISNULL([SundayPagerEndTime], 180000)
			,ISNULL([PagerDays], 0)
			,[NetsendAddress]
			,[CategoryName]
			,ISNULL([OverWrite], 1)
		FROM OPENXML(@docHandle, N'/Root/Operator') 
		WITH (
			[Name] sysname --no default
			,[Enabled] tinyint --default of 1 (enabled)
			,[EmailAddress] nvarchar(100) --default of NULL
			,[PagerAddress] nvarchar(100) --default of NULL
			,[WeekdayPagerStartTime] int ---default of 090000 (9:00 A.M)
			,[WeekdayPagerEndTime] int --default of 180000 (6:00 P.M)
			,[SaturdayPagerStartTime] int --default of 090000 (9:00 A.M)
			,[SaturdayPagerEndTime] int --default of 180000 (6:00 P.M)
			,[SundayPagerStartTime] int --default of 090000 (9:00 A.M)
			,[SundayPagerEndTime] int --default of 180000 (6:00 P.M)
			,[PagerDays] tinyint --default of 0 (1:Sunday;2:Monday;4:Tuesday;8:Wednesday;16:Thursday;32:Friday;64:Saturday)
			,[NetsendAddress] nvarchar(100) --default of NULL
			,[CategoryName] sysname --default of NULL
			,[OverWrite] bit --default of 1
		)
	)
	OPEN C_OPERATORS
	DECLARE @Name sysname, @Enabled tinyint, @EmailAddress nvarchar(100), @PagerAddress nvarchar(100),
		@WeekdayPagerStartTime int, @WeekdayPagerEndTime int, @SaturdayPagerStartTime int,
		@SaturdayPagerEndTime int, @SundayPagerStartTime int, @SundayPagerEndTime int,
		@PagerDays tinyint, @NetsendAddress nvarchar(100), @CategoryName sysname, @OverWrite bit;

	FETCH NEXT FROM C_OPERATORS INTO @Name, @Enabled, @EmailAddress, @PagerAddress, @WeekdayPagerStartTime,
		@WeekdayPagerEndTime, @SaturdayPagerStartTime, @SaturdayPagerEndTime, @SundayPagerStartTime,
		@SundayPagerEndTime, @PagerDays, @NetsendAddress, @CategoryName, @OverWrite;

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF NOT EXISTS(SELECT * FROM [msdb].[dbo].[sysoperators] WHERE ([name] = @Name))
			BEGIN
				EXEC [msdb].[dbo].[sp_add_operator]
					@name = @Name
					,@enabled = @Enabled
					,@email_address = @EmailAddress
					,@pager_address = @PagerAddress
					,@weekday_pager_start_time = @WeekdayPagerStartTime
					,@weekday_pager_end_time = @WeekdayPagerEndTime
					,@saturday_pager_start_time = @SaturdayPagerStartTime
					,@saturday_pager_end_time = @SaturdayPagerEndTime
					,@sunday_pager_start_time = @SundayPagerStartTime
					,@sunday_pager_end_time = @SundayPagerEndTime
					,@pager_days = @PagerDays
					,@netsend_address = @NetsendAddress
					,@category_name = @CategoryName;
			END
		ELSE IF(@OverWrite = 1)
			BEGIN
				EXEC [msdb].[dbo].[sp_update_operator]
					@name = @Name
					,@enabled = @Enabled
					,@email_address = @EmailAddress
					,@pager_address = @PagerAddress
					,@weekday_pager_start_time = @WeekdayPagerStartTime
					,@weekday_pager_end_time = @WeekdayPagerEndTime
					,@saturday_pager_start_time = @SaturdayPagerStartTime
					,@saturday_pager_end_time = @SaturdayPagerEndTime
					,@sunday_pager_start_time = @SundayPagerStartTime
					,@sunday_pager_end_time = @SundayPagerEndTime
					,@pager_days = @PagerDays
					,@netsend_address = @NetsendAddress
					,@category_name = @CategoryName;
			END

		FETCH NEXT FROM C_OPERATORS INTO @Name, @Enabled, @EmailAddress, @PagerAddress, @WeekdayPagerStartTime,
			@WeekdayPagerEndTime, @SaturdayPagerStartTime, @SaturdayPagerEndTime, @SundayPagerStartTime,
			@SundayPagerEndTime, @PagerDays, @NetsendAddress, @CategoryName, @OverWrite;
	END
	CLOSE C_OPERATORS
	DEALLOCATE C_OPERATORS

	-- cleanup xml
	EXEC sp_xml_removedocument @docHandle;
END